package Book;

import Operation.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    //1、新增书籍
    public boolean add(book book) {
//把book对象插入到数据库的BOOK表中
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            //1、建立数据库链接
            connection = DBUtil.getConnection();
            //2、拼装sql语句
            String sql = "insert into book values(null,?,?,?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1, book.getName());
            statement.setString(2, book.getAuthor());
            statement.setInt(3, book.getPrice());
            statement.setString(4, book.getType());
            statement.setInt(5, book.getIsBorrowed());
            //3、执行sql
            int ret = statement.executeUpdate();
            if (ret != 1) {
                //如果插入失败返回false。
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, null);
        }
        return false;
    }

    //2、查看所有书籍
    public List<book> selectAll() {
        List<book> books = new ArrayList<>();
        //获取数据库表中的所有书籍记录
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //1、先和数据库建立链接
            connection = DBUtil.getConnection();
            //2、拼接SQL
            String sql = "select * from book";
            statement = connection.prepareStatement(sql);
            //3、执行SQL
            resultSet = statement.executeQuery();
            //4、遍历结果集合
            while (resultSet.next()) {
                //取出当前行的这些列，构造出一个book对象来
                book book = new book();
                book.setBookId(resultSet.getInt("bookid"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setIsBorrowed(resultSet.getInt("isBrrowed"));
                books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
        return books;
    }

    //3、根据名字找书籍
    public List<book> selectByName(String name) {
        List<book> books = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //1、先和数据库建立连接
            connection = DBUtil.getConnection();
            //2、拼装SQL语句
            String sql = "select * from book where name= ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            //3、执行SQL
            resultSet = statement.executeQuery();
            //4、遍历结果集合
            while (resultSet.next()) {
                book book = new book();
                book.setBookId(resultSet.getInt("bookid"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setIsBorrowed(resultSet.getInt("isBrrowed"));
                books.add(book);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
        return books;
    }

    //4、删除书籍
    public boolean delete(int bookId) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            //1、建立链接
            connection = DBUtil.getConnection();
            //2、拼装SQL
            String sql = "delete from book where bookid= ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, bookId);
            //3、执行SQL
            int ret = statement.executeUpdate();
            if (ret != 1) {
                return false;
            }
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(connection, statement, null);
        }
        return false;
    }

    //5、借书
    public boolean borrowBook(int bookId) {
        Connection connection = null;
        PreparedStatement statement1 = null;
        PreparedStatement statement2 = null;
        ResultSet resultSet = null;
        try {
            //1、和数据库建立链接
            connection = DBUtil.getConnection();
            //从这里开始先进性查找书
            //2、拼装SQL
            //如果当前这里面的isBorrowed已经是1了，还能借吗？
            //如果这本书不存在还能借吗？
            //所以这里先查找，确实这本书存不存在，有没有被借出，再说别的
            String sql = "select * from book where bookid= ?";
            statement1 = connection.prepareStatement(sql);
            statement1.setInt(1, bookId);
            //3、执行SQL
            resultSet = statement1.executeQuery();
            //4、遍历结果，预期结果是1条或者是0
            if (resultSet.next()) {
                //因此这里只要if判断即可
                boolean isBorrowed = (resultSet.getInt("isBrrowed") == 1);
                if (isBorrowed) {
                    System.out.println("书已经借出！借不了哦 bookId=" + bookId);
                    return false;
                }
            } else {
                //到这里说明书不存在
                System.out.println("书不存在，借不了哦 bookId=" + bookId);
                return false;
            }
            //接下来才是真正的借书
            //5、拼装SQL
            sql = "update book set isBrrowed =1 where bookid = ?";
            statement2 = connection.prepareStatement(sql);
            statement2.setInt(1, bookId);
            //6、执行SQL
            int ret = statement2.executeUpdate();
            if (ret != 1) {
                return false;
            }
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement2 != null) {
                try {
                    statement2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement1 != null) {
                try {
                    statement1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }
    //6、还书
    public boolean returnBook (int bookId) throws SQLException {
        Connection connection = null;
        PreparedStatement statement1 = null;
        PreparedStatement statement2 = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection ();
            String sql = "select * from book where bookid = ?";
            statement1 = connection.prepareStatement (sql);
            statement1.setInt (1, bookId);
            resultSet = statement1.executeQuery ();
            if (resultSet.next ()) {
                //查询到书了
                boolean isBorrowed = (resultSet.getInt ("isBrrowed") == 1);
                if (!isBorrowed) {
                    System.out.println ("书没有借出，不用还 bookid=" + bookId);
                    return false;
                } else {
                    //书不存在
                    System.out.println ("书被借出 bookid=" + bookId);
                }
                //修改书的状态
                sql = "update book set isBrrowed = 0 where bookid = ?";
                statement2=connection.prepareStatement (sql);
                statement2.setInt (1, bookId);
                int ret=statement2.executeUpdate ();
                if(ret!=1){
                    return  false;
                }
                return true;
            }
        }catch (SQLException e){
            e.printStackTrace ();
        }finally {
            if (resultSet != null) {
                try {
                    resultSet.close ();
                } catch (SQLException e) {
                    e.printStackTrace ();
                }
            }
            if (statement2 != null) {
                try {
                    statement2.close ();
                } catch (SQLException e) {
                    e.printStackTrace ();
                }
            }
            if (statement1 != null) {
                try {
                    statement1.close ();
                } catch (SQLException e) {
                    e.printStackTrace ();
                }
            }
        }
        return  false;
    }
}



